package kr.or.kead.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.Book;
import kr.or.kead.domain.Member;
import kr.or.kead.util.Oracle_con;

public class DaoMember implements DaoTable {	
	
	/*CREATE TABLE member (
			mem_code char(4) primary key,
			mem_name varchar2(20) not null,
			tel char(13) not null,
			lend_count int default 0 not null
		);*/

	
	@Override
	public boolean insertDao(Object obj) {
		Connection conn = Oracle_con.getConnection();
		PreparedStatement pstm = null;
		String sql = "INSERT INTO member (mem_code, mem_name, tel) values (?, ?, ?)";	
		Member member = (Member) obj;
		try {			
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, member.getCode());
			pstm.setString(2, member.getName());
			pstm.setString(3, member.getTel());
			pstm.executeUpdate();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {			
			try {
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}

	@Override
	public boolean updateDao(Object obj) {
		Connection conn = Oracle_con.getConnection();
		PreparedStatement pstm = null;
		String sql = "UPDATE member set mem_code = ?, mem_name = ?, tel = ? where mem_code = ?";
		Member member = (Member) obj;
		try {			
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, member.getCode());
			pstm.setString(2, member.getName());
			pstm.setString(3, member.getTel());
			pstm.setString(4, member.getCode());
			pstm.executeUpdate();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {			
			try {
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}

	@Override
	public boolean deleteDao(String code) {
		Connection conn = Oracle_con.getConnection();
		PreparedStatement pstm = null;
		String sql = "DELETE FROM member WHERE mem_code = ?";
		
		try {			
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, code);			
			pstm.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {			
			try {
				pstm.close();
				conn.close();				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();				
			}			
		}
		return true;
	}
	
	public boolean updateCountDao(Object obj) {
		Connection conn = Oracle_con.getConnection();
		PreparedStatement pstm = null;
		String sql = "UPDATE member set mem_code = ?, mem_name = ?, tel = ?, lend_count =? where mem_code = ?";
		Member member = (Member) obj;
		try {			
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, member.getCode());
			pstm.setString(2, member.getName());
			pstm.setString(3, member.getTel());
			pstm.setInt(4, member.getLend_count());
			pstm.setString(5, member.getCode());
			pstm.executeUpdate();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {			
			try {
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}

	@Override
	public ArrayList<Object> selectAll() {		
		Connection conn = Oracle_con.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM member";
		ArrayList<Object> alBook = new ArrayList<Object>();
		Member member;
		
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()) {
				member = new Member(rs.getString("mem_code"), rs.getString("mem_name"), rs.getString("tel"));
			}
		} catch (SQLException e) {		
			e.printStackTrace();
			return null;
		} finally {			
			try {
				pstm.close();
				conn.close();				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();				
			}			
		}
		return alBook;
	}

	@Override
	public Object selectCode(String code) {
		Connection conn = Oracle_con.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM member WHERE mem_code = ?";
		Member mem = null;
		
		try {			
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, code);			
			rs = pstm.executeQuery();			
			if(rs.next()) {
				mem = new Member(rs.getString("mem_code"), rs.getString("mem_name"), rs.getString("tel"));
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {			
			try {
				pstm.close();
				conn.close();				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();				
			}			
		}
		return mem;
	}
	
	
	public Object selectName(String name) {
		Connection conn = Oracle_con.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String sql = "SELECT mem_code, mem_name, tel FROM member WHERE mem_name = ?";
		Member mem = null;
		
		try {			
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, name);			
			rs = pstm.executeQuery();			
			if(rs.next()) {
				mem = new Member(rs.getString("mem_code"), rs.getString("mem_name"), rs.getString("tel"));
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {			
			try {
				pstm.close();
				conn.close();				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();				
			}			
		}
		return mem;
	}

}
